import requests
from lxml import etree
import time
import pyodbc

#连接数据库
conn = pyodbc.connect(
        "DRIVER={ODBC Driver 17 for SQL Server};"
        "SERVER=LAPTOP-0U7HG0ED;"
        "DATABASE=pachongtest;"
        "Trusted_Connection=yes;"
    )
    # 创建了游标对象
cursor = conn.cursor()
cursor.execute('SELECT * FROM lianjia')

#爬取数据
for page in range(12,101):
    if page == 1:
        url = "https://nc.lianjia.com/ershoufang/"
    else:
        url = f"https://nc.lianjia.com/ershoufang/pg{page}/"
    for i in range(1,31):

        header = {"Cookie":"isw=1; isp=1; ism=1; HWWAFSESID=b320f760413af7bfad; HWWAFSESTIME=1719451774971; longTailKeyword=; province=%E5%B9%BF%E4%B8%9C; ipcity=guangzhou; city=%E5%B9%BF%E5%B7%9E; wafatcltime=2866202; wafatcltoken=3eeb9d6cb5e8b08c853cdfea3520ac64; hcbm_tk=MjZvkCuzGMQn8ACGsMpTwXY/gDFWiCY1Ohy1xraLNKOwFtguFKpbizn48pLakxtAhpwrK2r8BP2IWsUydkzM7ZOY8VTo3M+5HhN7TXxWU7FjaNF6K4JPblkRji9HGjP15dF3aSbW5OexOht073V9IDrt01sYfeQQGhDrNWuBDDZZzSmRD/hj5KSezCXGPHTnFIURX++ch50HxdhAMawNFAUHGnhJDpEyJCtKvycNrCUWIIDl2nz311e+0OPtKHTpIwM/+ISTSrkee2L800igBrXVhj2L8B3Krwz37x9DKdJ+CylYd7StxL6xGOLo62TOQZuttlI03q/kWXl9IYXN9m7R8C8ZLi06XF0OC1j+njXM57+yz6Vlw9+RkfL1xeCtXSs/x8OP4Jm5OD9p4LDgVe0I4I7hzYF8zqx0j5UeuFuTGj1WtbQLdY2YncPc6AnfB8dDy44qQZwmhIRBYQ==_RERXV4AmIa4VjY7e; JSESSIONID=98EAB748A71893CDB6BB1C6942E26221-n2.www",
            "User-Agent":"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36"}
        res = requests.get(url, headers=header)
        root = etree.HTML(res.text)
        #标题
        title = root.xpath(f'//*[@id="content"]/div[1]/ul/li[{i}]/div[1]/div[1]/a/text()')
        if len(title) == 0:
            i+=1
            continue
        #标题链接
        title_link = root.xpath(f'//*[@id="content"]/div[1]/ul/li[{i}]/div[1]/div[1]/a/@href')
        #缩略图链接
        imag_link = root.xpath(f'//*[@id="content"]/div[1]/ul/li[{i}]/div[1]/div[1]/a/@href')
        #地理位置
        location1 = root.xpath(f'//*[@id="content"]/div[1]/ul/li[{i}]/div[1]/div[2]/div/a[1]/text()')
        location2 = root.xpath(f'//*[@id="content"]/div[1]/ul/li[{i}]/div[1]/div[2]/div/a[2]/text()')
        #简介
        simple_describe = root.xpath(f"/html/body/div[4]/div[1]/ul/li[{i}]/div[1]/div[3]/div/text()")
        #特点标签
        tag1 = root.xpath(f'//*[@id="content"]/div[1]/ul/li[{i}]/div[1]/div[5]/span[1]/text()')
        tag2 = root.xpath(f'//*[@id="content"]/div[1]/ul/li[{i}]/div[1]/div[5]/span[2]/text()')
        tag3 = root.xpath(f'//*[@id="content"]/div[1]/ul/li[{i}]/div[1]/div[5]/span[3]/text()')
        combined_list = [str(item) for sublist in [tag1, tag2, tag3] for item in sublist]
        tag_list = '、'.join(combined_list)
        #房价
        price = root.xpath(f'//*[@id="content"]/div[1]/ul/li[{i}]/div[1]/div[6]/div[1]/span/text()')
        #平方价格
        per_price = root.xpath(f'//*[@id="content"]/div[1]/ul/li[{i}]/div[1]/div[6]/div[2]/span/text()')

        #获取房屋详情
        room_url = title_link[0]
        room_res = requests.get(room_url, headers=header)
        room_root = etree.HTML(room_res.text)

        base_list = []  #房屋基本属性
        trans_list = []     #房屋交易属性
        base_attribute =room_root.xpath('//*[@id="introduction"]/div/div/div[1]/div[2]/ul/li/text()')
        trans_attribute = room_root.xpath("//div[@class='transaction']//span[2]//text()")
        for j in range(len(base_attribute)):
            base_change = ''.join(base_attribute[j]).replace("\xa0", "").replace("\n", '').replace(" ", '')
            if base_change == '':
                continue
            else:
                base_list.append(base_change)

        for k in range(len(trans_attribute)):
            trans_change = ''.join(trans_attribute[k]).replace("\xa0", "").replace("\n", '').replace(" ", '')
            trans_list.append(trans_change)
        if len(trans_list) != 9:
            trans_list.append('无')

        #插入数据库
        try:
            SQL = f"""INSERT INTO lianjia (
          标题, 具体位置, 位置, 简介, 信息标签, 报价, 平方价, 标题链接, 缩略图链接,
          房屋户型, 所在楼层, 建筑面积, 户型结构, 套内面积, 建筑类型, 房屋朝向,
          建筑结构, 装修情况, 梯户比例, 配备电梯, 楼层高度, 挂牌时间, 交易权属,
          上次交易, 房屋用途, 房屋年限, 产权所属, 抵押信息, 房本备件, 房源核验码
        ) VALUES (
          '{title[0]}', '{location1[0]}', '{location2[0]}', '{simple_describe[0]}', '{tag_list}', '{price[0]}', '{per_price[0]}',
          '{title_link[0]}', '{imag_link[0]}',
          '{base_list[0]}', '{base_list[1]}', '{base_list[2]}', '{base_list[3]}', '{base_list[4]}', '{base_list[5]}', '{base_list[6]}',
          '{base_list[7]}', '{base_list[8]}', '{base_list[9]}', '{base_list[10]}', '{base_list[11]}', '{trans_list[0]}', '{trans_list[1]}',
          '{trans_list[2]}', '{trans_list[3]}', '{trans_list[4]}', '{trans_list[5]}', '{trans_list[6]}', '{trans_list[7]}', '{trans_list[8]}'
        );"""
        except:
            print(f"第{page}页第{i}个:{title}数据插入失败")
            continue
        cursor = conn.cursor()
        cursor.execute(SQL)
        conn.commit()
        print(f"第{page}页第{i}个:{title}数据插入成功")
    time.sleep(0.5)